package com.sqlcheck.service.impl;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.sqlcheck.entity.ScanTask;
import com.sqlcheck.model.SqlStatement;
import com.sqlcheck.repository.ScanTaskRepository;
import com.sqlcheck.service.SqlContentExtractionService;
import com.sqlcheck.service.SqlExtractionResult;
import com.sqlcheck.service.TemporaryCredentialService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;

import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 数据库DDL SQL内容提取服务实现
 */
@Slf4j
@Service("databaseDdlSqlExtractionService")
@RequiredArgsConstructor
public class DatabaseDdlSqlContentExtractionServiceImpl implements SqlContentExtractionService {

    private final ObjectMapper objectMapper;
    private final TemporaryCredentialService temporaryCredentialService;
    private final ScanTaskRepository scanTaskRepository;

    @Override
    public SqlExtractionResult extractSqlContent(ScanTask task) {
        long startTime = System.currentTimeMillis();

        try {
            log.info("开始提取数据库DDL: taskId={}", task.getId());

            // 1. 解析数据库扫描配置
            DatabaseScanConfig config = parseDatabaseScanConfig(task.getConfigJson(), task);

            // 2. 连接数据库并提取DDL
            List<SqlStatement> ddlStatements = extractDdlStatements(config);

            // 3. 为语句分配全局编号
            assignGlobalStatementNumbers(ddlStatements);

            // 4. 构建提取元数据
            long extractionTime = System.currentTimeMillis() - startTime;
            SqlExtractionResult.ExtractionMetadata metadata = buildDdlExtractionMetadata(
                    config, ddlStatements, extractionTime);

            // 5. 保存提取统计信息到任务中
            saveExtractionStatsToTask(task, ddlStatements, config, extractionTime);

            log.info("数据库DDL提取完成: taskId={}, 提取到{}条DDL语句, 耗时{}ms",
                    task.getId(), ddlStatements.size(), extractionTime);

            return SqlExtractionResult.success(ddlStatements, metadata);

        } catch (Exception e) {
            log.error("数据库DDL提取失败: taskId={}", task.getId(), e);
            String errorMessage = e.getMessage() != null ? e.getMessage() : e.getClass().getSimpleName();
            return SqlExtractionResult.failure("DDL提取失败: " + errorMessage);
        }
    }

    /**
     * 解析数据库扫描配置
     */
    private DatabaseScanConfig parseDatabaseScanConfig(String configJson, ScanTask task) {
        try {
            if (configJson == null || configJson.trim().isEmpty()) {
                throw new IllegalArgumentException("数据库扫描配置为空");
            }

            JsonNode jsonNode = objectMapper.readTree(configJson);
            DatabaseScanConfig config = new DatabaseScanConfig();

            // 数据库连接信息
            config.setHost(getJsonString(jsonNode, "databaseHost"));
            config.setPort(getJsonInt(jsonNode, "databasePort", 3306));
            config.setDatabase(getJsonString(jsonNode, "databaseName"));
            config.setSchema(getJsonString(jsonNode, "databaseSchema"));
            config.setUsername(getJsonString(jsonNode, "databaseUsername"));
            config.setPassword(getJsonString(jsonNode, "databasePassword"));
            log.debug("从JSON配置读取密码: taskId={}, passwordFromJson={}", task.getId(),
                    config.getPassword() != null ? "[已设置]" : "[未设置]");

            // 如果配置中没有密码，尝试从临时凭据服务获取
            if (config.getPassword() == null || config.getPassword().trim().isEmpty()) {
                log.debug("JSON配置中没有密码，尝试从临时凭据服务获取: taskId={}", task.getId());
                try {
                    String tempPassword = temporaryCredentialService.getDatabasePassword(task.getId());
                    log.debug("临时凭据服务返回密码: taskId={}, passwordFromService={}", task.getId(),
                            tempPassword != null ? "[已获取]" : "[为空]");
                    if (tempPassword != null && !tempPassword.trim().isEmpty()) {
                        config.setPassword(tempPassword);
                        log.debug("从临时凭据服务获取到数据库密码: taskId={}", task.getId());
                    } else {
                        log.warn("临时凭据服务未返回有效密码: taskId={}", task.getId());
                    }
                } catch (Exception e) {
                    log.warn("从临时凭据服务获取数据库密码失败: taskId={}, error={}", task.getId(), e.getMessage());
                }
            }

            // 最终检查密码是否获取成功
            if (config.getPassword() == null || config.getPassword().trim().isEmpty()) {
                log.error("数据库密码为空 - 检查任务配置: taskId={}, configJson={}", task.getId(), task.getConfigJson());
                throw new IllegalArgumentException("数据库密码不能为空。请检查任务配置或临时凭据服务是否正常工作。");
            }
            config.setDbType(getJsonString(jsonNode, "sourceDbType", "MYSQL"));
            config.setDbVersion(getJsonString(jsonNode, "databaseVersion"));
            config.setOracleConnectionType(getJsonString(jsonNode, "oracleConnectionType", "SERVICE_NAME"));

            // DDL提取选项
            config.setIncludeTables(getJsonBoolean(jsonNode, "ddlIncludeTables", true));
            config.setIncludeViews(getJsonBoolean(jsonNode, "ddlIncludeViews", true));
            config.setIncludeIndexes(getJsonBoolean(jsonNode, "ddlIncludeIndexes", true));
            config.setIncludeConstraints(getJsonBoolean(jsonNode, "ddlIncludeConstraints", true));
            config.setIncludeProcedures(getJsonBoolean(jsonNode, "ddlIncludeProcedures", false));
            config.setIncludeFunctions(getJsonBoolean(jsonNode, "ddlIncludeFunctions", false));
            config.setIncludeTriggers(getJsonBoolean(jsonNode, "ddlIncludeTriggers", false));

            // 目标表名列表
            if (jsonNode.has("ddlTargetTables") && !jsonNode.get("ddlTargetTables").isNull()) {
                String targetTablesStr = jsonNode.get("ddlTargetTables").asText();
                if (targetTablesStr != null && !targetTablesStr.trim().isEmpty()) {
                    config.setTargetTables(Arrays.stream(targetTablesStr.split(","))
                            .map(String::trim)
                            .filter(s -> !s.isEmpty())
                            .collect(Collectors.toList()));
                }
            }

            return config;

        } catch (Exception e) {
            throw new RuntimeException("解析数据库扫描配置失败: " + e.getMessage(), e);
        }
    }

    /**
     * 提取DDL语句
     */
    private List<SqlStatement> extractDdlStatements(DatabaseScanConfig config) {
        List<SqlStatement> statements = new ArrayList<>();

        try (Connection conn = createDatabaseConnection(config)) {
            log.info("数据库连接成功: {}:{}/{}", config.getHost(), config.getPort(), config.getDatabase());

            // 输出数据库基本信息
            logDatabaseInfo(conn, config);

            // 提取表DDL
            if (config.isIncludeTables()) {
                List<SqlStatement> tableStatements = extractTableDdl(conn, config);
                statements.addAll(tableStatements);
                log.info("===== 表DDL提取完成: 共{}个表 =====", tableStatements.size());
            }

            // 提取视图DDL
            if (config.isIncludeViews()) {
                List<SqlStatement> viewStatements = extractViewDdl(conn, config);
                statements.addAll(viewStatements);
                log.info("===== 视图DDL提取完成: 共{}个视图 =====", viewStatements.size());
            }

            // 提取索引DDL
            if (config.isIncludeIndexes()) {
                List<SqlStatement> indexStatements = extractIndexDdl(conn, config);
                statements.addAll(indexStatements);
                log.info("===== 索引DDL提取完成: 共{}个索引 =====", indexStatements.size());
            }

            // 提取约束DDL
            if (config.isIncludeConstraints()) {
                List<SqlStatement> constraintStatements = extractConstraintDdl(conn, config);
                statements.addAll(constraintStatements);
                log.info("===== 约束DDL提取完成: 共{}个约束 =====", constraintStatements.size());
            }

            // 提取存储过程DDL
            if (config.isIncludeProcedures()) {
                List<SqlStatement> procedureStatements = extractProcedureDdl(conn, config);
                statements.addAll(procedureStatements);
                log.info("===== 存储过程DDL提取完成: 共{}个存储过程 =====", procedureStatements.size());
            }

            // 提取函数DDL
            if (config.isIncludeFunctions()) {
                List<SqlStatement> functionStatements = extractFunctionDdl(conn, config);
                statements.addAll(functionStatements);
                log.info("===== 函数DDL提取完成: 共{}个函数 =====", functionStatements.size());
            }

            // 提取触发器DDL
            if (config.isIncludeTriggers()) {
                List<SqlStatement> triggerStatements = extractTriggerDdl(conn, config);
                statements.addAll(triggerStatements);
                log.info("===== 触发器DDL提取完成: 共{}个触发器 =====", triggerStatements.size());
            }

            // 输出最终统计信息
            logExtractionSummary(statements, config);

        } catch (SQLException e) {
            throw new RuntimeException("数据库DDL提取失败: " + e.getMessage(), e);
        }

        return statements;
    }

    /**
     * 创建数据库连接
     */
    private Connection createDatabaseConnection(DatabaseScanConfig config) throws SQLException {
        String jdbcUrl = buildJdbcUrl(config);
        Properties props = new Properties();

        // 检查用户名和密码是否为空
        if (config.getUsername() == null || config.getUsername().trim().isEmpty()) {
            throw new SQLException("数据库用户名不能为空");
        }
        if (config.getPassword() == null || config.getPassword().trim().isEmpty()) {
            throw new SQLException("数据库密码不能为空");
        }

        props.setProperty("user", config.getUsername());
        props.setProperty("password", config.getPassword());

        // 设置连接超时
        props.setProperty("connectTimeout", "30000");
        props.setProperty("socketTimeout", "30000");

        return DriverManager.getConnection(jdbcUrl, props);
    }

    /**
     * 构建JDBC连接URL
     */
    private String buildJdbcUrl(DatabaseScanConfig config) {
        String dbType = config.getDbType().toUpperCase();

        switch (dbType) {
            case "MYSQL":
                String mysqlUrl = String.format("jdbc:mysql://%s:%d/%s",
                        config.getHost(), config.getPort(), config.getDatabase());
                mysqlUrl += "?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&useInformationSchema=true";
                return mysqlUrl;

            case "ORACLE":
                // Oracle 支持两种连接方式：
                // 1. SERVICE_NAME (推荐): jdbc:oracle:thin:@//host:port/service_name
                // 2. SID (旧版): jdbc:oracle:thin:@host:port:sid
                String connectionType = config.getOracleConnectionType();
                String dbIdentifier = config.getDatabase();

                if ("SID".equalsIgnoreCase(connectionType)) {
                    // 使用 SID 方式
                    log.info("使用 Oracle SID 连接方式: {}", dbIdentifier);
                    return String.format("jdbc:oracle:thin:@%s:%d:%s",
                            config.getHost(), config.getPort(), dbIdentifier);
                } else {
                    // 默认使用 SERVICE_NAME 方式
                    log.info("使用 Oracle SERVICE_NAME 连接方式: {}", dbIdentifier);
                    return String.format("jdbc:oracle:thin:@//%s:%d/%s",
                            config.getHost(), config.getPort(), dbIdentifier);
                }

            default:
                throw new IllegalArgumentException("不支持的数据库类型: " + dbType);
        }
    }

    /**
     * 提取表DDL
     */
    private List<SqlStatement> extractTableDdl(Connection conn, DatabaseScanConfig config) {
        List<SqlStatement> tableStatements = new ArrayList<>();

        try {
            List<String> tableNames = getTableNames(conn, config);
            log.info("找到 {} 个表需要提取DDL", tableNames.size());

            // 详细记录每个表的信息
            if (!tableNames.isEmpty()) {
                log.info("===== 表列表详情 =====");
                for (int i = 0; i < tableNames.size(); i++) {
                    String tableName = tableNames.get(i);
                    log.info("表 {}: {}", i + 1, tableName);

                    // 获取表的基本信息
                    logTableInfo(conn, tableName, config);
                }
                log.info("===== 表列表结束 =====");
            }

            for (String tableName : tableNames) {
                String createTableSql = getCreateTableStatement(conn, tableName, config);
                if (createTableSql != null && !createTableSql.trim().isEmpty()) {
                    log.debug("提取到表 {} 的DDL: {}", tableName,
                            createTableSql.length() > 200 ? createTableSql.substring(0, 200) + "..." : createTableSql);

                    SqlStatement statement = SqlStatement.builder()
                            .content(createTableSql)
                            .sourceFile("TABLE:" + tableName)
                            .sourceType(SqlStatement.SourceType.DDL_SCRIPT)
                            .sqlType(SqlStatement.SqlType.DDL)
                            .lineNumber(1)
                            .build();

                    tableStatements.add(statement);
                } else {
                    log.warn("无法获取表 {} 的CREATE语句", tableName);
                }
            }

        } catch (SQLException e) {
            log.error("提取表DDL失败", e);
        }

        return tableStatements;
    }

    /**
     * 提取视图DDL
     */
    private List<SqlStatement> extractViewDdl(Connection conn, DatabaseScanConfig config) {
        List<SqlStatement> viewStatements = new ArrayList<>();

        try {
            List<String> viewNames = getViewNames(conn, config);
            log.info("找到 {} 个视图需要提取DDL", viewNames.size());

            // 详细记录每个视图的信息
            if (!viewNames.isEmpty()) {
                log.info("===== 视图列表详情 =====");
                for (int i = 0; i < viewNames.size(); i++) {
                    String viewName = viewNames.get(i);
                    log.info("视图 {}: {}", i + 1, viewName);
                }
                log.info("===== 视图列表结束 =====");
            }

            for (String viewName : viewNames) {
                String createViewSql = getCreateViewStatement(conn, viewName, config);
                if (createViewSql != null && !createViewSql.trim().isEmpty()) {
                    log.debug("提取到视图 {} 的DDL: {}", viewName,
                            createViewSql.length() > 200 ? createViewSql.substring(0, 200) + "..." : createViewSql);

                    SqlStatement statement = SqlStatement.builder()
                            .content(createViewSql)
                            .sourceFile("VIEW:" + viewName)
                            .sourceType(SqlStatement.SourceType.DDL_SCRIPT)
                            .sqlType(SqlStatement.SqlType.DDL)
                            .lineNumber(1)
                            .build();

                    viewStatements.add(statement);
                } else {
                    log.warn("无法获取视图 {} 的CREATE语句", viewName);
                }
            }

        } catch (SQLException e) {
            log.error("提取视图DDL失败", e);
        }

        return viewStatements;
    }

    /**
     * 提取索引DDL
     */
    private List<SqlStatement> extractIndexDdl(Connection conn, DatabaseScanConfig config) {
        List<SqlStatement> indexStatements = new ArrayList<>();

        try {
            Map<String, String> indexDdlMap = getIndexDdlMap(conn, config);
            log.info("找到 {} 个索引需要提取DDL", indexDdlMap.size());

            for (Map.Entry<String, String> entry : indexDdlMap.entrySet()) {
                String indexName = entry.getKey();
                String createIndexSql = entry.getValue();

                if (createIndexSql != null && !createIndexSql.trim().isEmpty()) {
                    SqlStatement statement = SqlStatement.builder()
                            .content(createIndexSql)
                            .sourceFile("INDEX:" + indexName)
                            .sourceType(SqlStatement.SourceType.DDL_SCRIPT)
                            .sqlType(SqlStatement.SqlType.DDL)
                            .lineNumber(1)
                            .build();

                    indexStatements.add(statement);
                }
            }

        } catch (SQLException e) {
            log.error("提取索引DDL失败", e);
        }

        return indexStatements;
    }

    /**
     * 提取约束DDL
     */
    private List<SqlStatement> extractConstraintDdl(Connection conn, DatabaseScanConfig config) {
        List<SqlStatement> constraintStatements = new ArrayList<>();

        try {
            Map<String, String> constraintDdlMap = getConstraintDdlMap(conn, config);
            log.info("找到 {} 个约束需要提取DDL", constraintDdlMap.size());

            for (Map.Entry<String, String> entry : constraintDdlMap.entrySet()) {
                String constraintName = entry.getKey();
                String createConstraintSql = entry.getValue();

                if (createConstraintSql != null && !createConstraintSql.trim().isEmpty()) {
                    SqlStatement statement = SqlStatement.builder()
                            .content(createConstraintSql)
                            .sourceFile("CONSTRAINT:" + constraintName)
                            .sourceType(SqlStatement.SourceType.DDL_SCRIPT)
                            .sqlType(SqlStatement.SqlType.DDL)
                            .lineNumber(1)
                            .build();

                    constraintStatements.add(statement);
                }
            }

        } catch (SQLException e) {
            log.error("提取约束DDL失败", e);
        }

        return constraintStatements;
    }

    /**
     * 提取存储过程DDL
     */
    private List<SqlStatement> extractProcedureDdl(Connection conn, DatabaseScanConfig config) {
        // 实现存储过程DDL提取逻辑
        return new ArrayList<>();
    }

    /**
     * 提取函数DDL
     */
    private List<SqlStatement> extractFunctionDdl(Connection conn, DatabaseScanConfig config) {
        // 实现函数DDL提取逻辑
        return new ArrayList<>();
    }

    /**
     * 提取触发器DDL
     */
    private List<SqlStatement> extractTriggerDdl(Connection conn, DatabaseScanConfig config) {
        // 实现触发器DDL提取逻辑
        return new ArrayList<>();
    }

    // === MySQL/Oracle 具体实现方法 ===

    /**
     * 获取表名列表
     */
    private List<String> getTableNames(Connection conn, DatabaseScanConfig config) throws SQLException {
        List<String> tableNames = new ArrayList<>();
        String dbType = config.getDbType().toUpperCase();

        String sql;
        if ("MYSQL".equals(dbType)) {
            sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES " +
                    "WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'";
        } else if ("ORACLE".equals(dbType)) {
            sql = "SELECT TABLE_NAME FROM USER_TABLES";
        } else {
            throw new UnsupportedOperationException("不支持的数据库类型: " + dbType);
        }

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            if ("MYSQL".equals(dbType)) {
                stmt.setString(1, config.getDatabase());
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String tableName = rs.getString("TABLE_NAME");

                    // 如果指定了目标表，只提取指定的表
                    if (config.getTargetTables() == null || config.getTargetTables().isEmpty() ||
                            config.getTargetTables().contains(tableName)) {
                        tableNames.add(tableName);
                    }
                }
            }
        }

        return tableNames;
    }

    /**
     * 获取CREATE TABLE语句
     */
    private String getCreateTableStatement(Connection conn, String tableName, DatabaseScanConfig config) {
        String dbType = config.getDbType().toUpperCase();

        try {
            if ("MYSQL".equals(dbType)) {
                return getMySqlCreateTable(conn, tableName);
            } else if ("ORACLE".equals(dbType)) {
                return getOracleCreateTable(conn, tableName);
            } else {
                throw new UnsupportedOperationException("不支持的数据库类型: " + dbType);
            }
        } catch (SQLException e) {
            log.error("获取表 {} 的CREATE语句失败", tableName, e);
            return null;
        }
    }

    private String getMySqlCreateTable(Connection conn, String tableName) throws SQLException {
        // 使用反引号包围表名，防止reserved关键字问题
        String sql = "SHOW CREATE TABLE `" + tableName + "`";
        try (Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getString(2); // CREATE TABLE语句在第二列
            }
        }
        return null;
    }

    private String getOracleCreateTable(Connection conn, String tableName) throws SQLException {
        // 使用DBMS_METADATA.GET_DDL获取Oracle表DDL
        String sql = "SELECT DBMS_METADATA.GET_DDL('TABLE', ?) FROM DUAL";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, tableName);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getString(1);
                }
            }
        }
        return null;
    }

    /**
     * 获取视图名列表
     */
    private List<String> getViewNames(Connection conn, DatabaseScanConfig config) throws SQLException {
        List<String> viewNames = new ArrayList<>();
        String dbType = config.getDbType().toUpperCase();

        String sql;
        if ("MYSQL".equals(dbType)) {
            sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = ?";
        } else if ("ORACLE".equals(dbType)) {
            sql = "SELECT VIEW_NAME FROM USER_VIEWS";
        } else {
            throw new UnsupportedOperationException("不支持的数据库类型: " + dbType);
        }

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            if ("MYSQL".equals(dbType)) {
                stmt.setString(1, config.getDatabase());
            }

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    viewNames.add(rs.getString(1));
                }
            }
        }

        return viewNames;
    }

    /**
     * 获取CREATE VIEW语句
     */
    private String getCreateViewStatement(Connection conn, String viewName, DatabaseScanConfig config) {
        String dbType = config.getDbType().toUpperCase();

        try {
            if ("MYSQL".equals(dbType)) {
                return getMySqlCreateView(conn, viewName);
            } else if ("ORACLE".equals(dbType)) {
                return getOracleCreateView(conn, viewName);
            } else {
                throw new UnsupportedOperationException("不支持的数据库类型: " + dbType);
            }
        } catch (SQLException e) {
            log.error("获取视图 {} 的CREATE语句失败", viewName, e);
            return null;
        }
    }

    private String getMySqlCreateView(Connection conn, String viewName) throws SQLException {
        String sql = "SHOW CREATE VIEW " + viewName;
        try (Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getString(2); // CREATE VIEW语句在第二列
            }
        }
        return null;
    }

    private String getOracleCreateView(Connection conn, String viewName) throws SQLException {
        String sql = "SELECT DBMS_METADATA.GET_DDL('VIEW', ?) FROM DUAL";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, viewName);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getString(1);
                }
            }
        }
        return null;
    }

    /**
     * 获取索引DDL映射
     */
    private Map<String, String> getIndexDdlMap(Connection conn, DatabaseScanConfig config) throws SQLException {
        Map<String, String> indexDdlMap = new HashMap<>();
        String dbType = config.getDbType().toUpperCase();

        if ("MYSQL".equals(dbType)) {
            return getMySqlIndexDdl(conn, config);
        } else if ("ORACLE".equals(dbType)) {
            return getOracleIndexDdl(conn, config);
        }

        return indexDdlMap;
    }

    private Map<String, String> getMySqlIndexDdl(Connection conn, DatabaseScanConfig config) throws SQLException {
        Map<String, String> indexDdlMap = new HashMap<>();

        String sql = "SELECT DISTINCT INDEX_NAME, TABLE_NAME " +
                "FROM INFORMATION_SCHEMA.STATISTICS " +
                "WHERE TABLE_SCHEMA = ? AND INDEX_NAME != 'PRIMARY'";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, config.getDatabase());

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String indexName = rs.getString("INDEX_NAME");
                    String tableName = rs.getString("TABLE_NAME");

                    // 构造CREATE INDEX语句（简化版）
                    String createIndexSql = String.format("CREATE INDEX %s ON %s (...)", indexName, tableName);
                    indexDdlMap.put(indexName, createIndexSql);
                }
            }
        }

        return indexDdlMap;
    }

    private Map<String, String> getOracleIndexDdl(Connection conn, DatabaseScanConfig config) throws SQLException {
        Map<String, String> indexDdlMap = new HashMap<>();

        String sql = "SELECT INDEX_NAME FROM USER_INDEXES WHERE INDEX_TYPE = 'NORMAL'";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String indexName = rs.getString("INDEX_NAME");
                    try {
                        String ddl = getOracleObjectDdl(conn, "INDEX", indexName);
                        if (ddl != null) {
                            indexDdlMap.put(indexName, ddl);
                        }
                    } catch (SQLException e) {
                        log.warn("获取Oracle索引 {} DDL失败: {}", indexName, e.getMessage());
                    }
                }
            }
        }

        return indexDdlMap;
    }

    /**
     * 获取约束DDL映射
     */
    private Map<String, String> getConstraintDdlMap(Connection conn, DatabaseScanConfig config) throws SQLException {
        Map<String, String> constraintDdlMap = new HashMap<>();
        String dbType = config.getDbType().toUpperCase();

        if ("MYSQL".equals(dbType)) {
            return getMySqlConstraintDdl(conn, config);
        } else if ("ORACLE".equals(dbType)) {
            return getOracleConstraintDdl(conn, config);
        }

        return constraintDdlMap;
    }

    private Map<String, String> getMySqlConstraintDdl(Connection conn, DatabaseScanConfig config) throws SQLException {
        Map<String, String> constraintDdlMap = new HashMap<>();

        String sql = "SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE " +
                "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS " +
                "WHERE TABLE_SCHEMA = ? AND CONSTRAINT_TYPE IN ('FOREIGN KEY', 'CHECK')";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, config.getDatabase());

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String constraintName = rs.getString("CONSTRAINT_NAME");
                    String tableName = rs.getString("TABLE_NAME");
                    String constraintType = rs.getString("CONSTRAINT_TYPE");

                    // 构造ALTER TABLE ADD CONSTRAINT语句（简化版）
                    String createConstraintSql = String.format("ALTER TABLE %s ADD CONSTRAINT %s %s (...)",
                            tableName, constraintName, constraintType);
                    constraintDdlMap.put(constraintName, createConstraintSql);
                }
            }
        }

        return constraintDdlMap;
    }

    private Map<String, String> getOracleConstraintDdl(Connection conn, DatabaseScanConfig config) throws SQLException {
        Map<String, String> constraintDdlMap = new HashMap<>();

        String sql = "SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE IN ('R', 'C')";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String constraintName = rs.getString("CONSTRAINT_NAME");
                    try {
                        String ddl = getOracleObjectDdl(conn, "CONSTRAINT", constraintName);
                        if (ddl != null) {
                            constraintDdlMap.put(constraintName, ddl);
                        }
                    } catch (SQLException e) {
                        log.warn("获取Oracle约束 {} DDL失败: {}", constraintName, e.getMessage());
                    }
                }
            }
        }

        return constraintDdlMap;
    }

    /**
     * 获取Oracle对象DDL
     */
    private String getOracleObjectDdl(Connection conn, String objectType, String objectName) throws SQLException {
        String sql = "SELECT DBMS_METADATA.GET_DDL(?, ?) FROM DUAL";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, objectType);
            stmt.setString(2, objectName);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getString(1);
                }
            }
        }
        return null;
    }

    /**
     * 为语句分配全局编号
     */
    private void assignGlobalStatementNumbers(List<SqlStatement> statements) {
        for (int i = 0; i < statements.size(); i++) {
            statements.get(i).setStatementNumber(i + 1);
        }
    }

    /**
     * 构建DDL提取元数据
     */
    private SqlExtractionResult.ExtractionMetadata buildDdlExtractionMetadata(
            DatabaseScanConfig config, List<SqlStatement> statements, long extractionTime) {

        Map<String, Integer> sourceTypeCounts = statements.stream()
                .collect(Collectors.groupingBy(
                        stmt -> getDdlObjectType(stmt.getSourceFile()),
                        Collectors.collectingAndThen(Collectors.counting(), Math::toIntExact)));

        Map<String, Object> additionalInfo = new HashMap<>();
        additionalInfo.put("dbType", config.getDbType());
        additionalInfo.put("dbVersion", config.getDbVersion());
        additionalInfo.put("includeTables", config.isIncludeTables());
        additionalInfo.put("includeViews", config.isIncludeViews());
        additionalInfo.put("includeIndexes", config.isIncludeIndexes());
        additionalInfo.put("includeConstraints", config.isIncludeConstraints());

        String sourceLocation = String.format("%s:%d/%s", config.getHost(), config.getPort(), config.getDatabase());
        if (config.getSchema() != null) {
            sourceLocation += "." + config.getSchema();
        }

        return SqlExtractionResult.ExtractionMetadata.builder()
                .extractionType("DATABASE_DDL_SCAN")
                .sourceLocation(sourceLocation)
                .totalStatements(statements.size())
                .sourceTypeCounts(sourceTypeCounts)
                .extractionTimeMs(extractionTime)
                .additionalInfo(additionalInfo)
                .build();
    }

    /**
     * 从源文件名提取DDL对象类型
     */
    private String getDdlObjectType(String sourceFile) {
        if (sourceFile.startsWith("TABLE:"))
            return "表";
        if (sourceFile.startsWith("VIEW:"))
            return "视图";
        if (sourceFile.startsWith("INDEX:"))
            return "索引";
        if (sourceFile.startsWith("CONSTRAINT:"))
            return "约束";
        if (sourceFile.startsWith("PROCEDURE:"))
            return "存储过程";
        if (sourceFile.startsWith("FUNCTION:"))
            return "函数";
        if (sourceFile.startsWith("TRIGGER:"))
            return "触发器";
        return "其他";
    }

    // === 辅助方法 ===

    private String getJsonString(JsonNode node, String fieldName) {
        return getJsonString(node, fieldName, null);
    }

    private String getJsonString(JsonNode node, String fieldName, String defaultValue) {
        if (node.has(fieldName) && !node.get(fieldName).isNull()) {
            return node.get(fieldName).asText();
        }
        return defaultValue;
    }

    private int getJsonInt(JsonNode node, String fieldName, int defaultValue) {
        if (node.has(fieldName) && !node.get(fieldName).isNull()) {
            return node.get(fieldName).asInt(defaultValue);
        }
        return defaultValue;
    }

    private boolean getJsonBoolean(JsonNode node, String fieldName, boolean defaultValue) {
        if (node.has(fieldName) && !node.get(fieldName).isNull()) {
            return node.get(fieldName).asBoolean(defaultValue);
        }
        return defaultValue;
    }

    /**
     * 记录数据库基本信息
     */
    private void logDatabaseInfo(Connection conn, DatabaseScanConfig config) {
        try {
            DatabaseMetaData metaData = conn.getMetaData();
            String productName = metaData.getDatabaseProductName();
            String productVersion = metaData.getDatabaseProductVersion();
            String driverName = metaData.getDriverName();
            String driverVersion = metaData.getDriverVersion();

            log.info("==================== 数据库连接信息 ====================");
            log.info("数据库产品: {}", productName);
            log.info("数据库版本: {}", productVersion);
            log.info("JDBC驱动: {} {}", driverName, driverVersion);
            log.info("目标数据库: {}", config.getDatabase());
            if (config.getSchema() != null && !config.getSchema().trim().isEmpty()) {
                log.info("目标Schema: {}", config.getSchema());
            }
            log.info("连接地址: {}:{}", config.getHost(), config.getPort());
            log.info("连接用户: {}", config.getUsername());

            // 统计数据库中的对象数量
            logDatabaseObjectCounts(conn, config);

            log.info("=====================================================");

        } catch (SQLException e) {
            log.warn("无法获取数据库基本信息: {}", e.getMessage());
        }
    }

    /**
     * 统计数据库对象数量
     */
    private void logDatabaseObjectCounts(Connection conn, DatabaseScanConfig config) {
        try {
            String dbType = config.getDbType().toUpperCase();

            // 统计表数量
            int tableCount = countTables(conn, config);
            log.info("数据库表总数: {}", tableCount);

            // 统计视图数量
            int viewCount = countViews(conn, config);
            log.info("数据库视图总数: {}", viewCount);

            // 统计索引数量
            int indexCount = countIndexes(conn, config);
            log.info("数据库索引总数: {}", indexCount);

            if ("MYSQL".equals(dbType)) {
                // MySQL特有对象统计
                int procedureCount = countProcedures(conn, config);
                log.info("存储过程总数: {}", procedureCount);

                int functionCount = countFunctions(conn, config);
                log.info("函数总数: {}", functionCount);

                int triggerCount = countTriggers(conn, config);
                log.info("触发器总数: {}", triggerCount);
            }

        } catch (Exception e) {
            log.warn("统计数据库对象数量失败: {}", e.getMessage());
        }
    }

    /**
     * 记录表的详细信息
     */
    private void logTableInfo(Connection conn, String tableName, DatabaseScanConfig config) {
        try {
            String dbType = config.getDbType().toUpperCase();

            if ("MYSQL".equals(dbType)) {
                // 获取MySQL表信息
                String sql = "SELECT ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, TABLE_COLLATION " +
                        "FROM INFORMATION_SCHEMA.TABLES " +
                        "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";

                try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                    stmt.setString(1, config.getDatabase());
                    stmt.setString(2, tableName);

                    try (ResultSet rs = stmt.executeQuery()) {
                        if (rs.next()) {
                            String engine = rs.getString("ENGINE");
                            long tableRows = rs.getLong("TABLE_ROWS");
                            long dataLength = rs.getLong("DATA_LENGTH");
                            long indexLength = rs.getLong("INDEX_LENGTH");
                            String collation = rs.getString("TABLE_COLLATION");

                            log.info("  └─ 存储引擎: {}, 行数: {}, 数据大小: {}B, 索引大小: {}B, 字符集: {}",
                                    engine, tableRows, dataLength, indexLength, collation);
                        }
                    }
                }
            }

            // 获取列数量
            int columnCount = countTableColumns(conn, tableName, config);
            log.info("  └─ 列数: {}", columnCount);

        } catch (Exception e) {
            log.debug("获取表 {} 详细信息失败: {}", tableName, e.getMessage());
        }
    }

    /**
     * 记录提取汇总信息
     */
    private void logExtractionSummary(List<SqlStatement> statements, DatabaseScanConfig config) {
        log.info("==================== DDL提取汇总 ====================");
        log.info("目标数据库: {}:{}/{}", config.getHost(), config.getPort(), config.getDatabase());
        log.info("数据库类型: {}", config.getDbType());
        if (config.getDbVersion() != null) {
            log.info("数据库版本: {}", config.getDbVersion());
        }

        // 按类型统计DDL语句
        Map<String, Long> typeCounts = statements.stream()
                .collect(Collectors.groupingBy(
                        stmt -> getDdlObjectType(stmt.getSourceFile()),
                        Collectors.counting()));

        log.info("提取结果统计:");
        typeCounts.forEach((type, count) -> log.info("  {} : {} 个", type, count));

        log.info("DDL语句总数: {}", statements.size());

        // 统计DDL内容大小
        long totalSize = statements.stream()
                .mapToLong(stmt -> stmt.getContent() != null ? stmt.getContent().length() : 0)
                .sum();
        log.info("DDL内容总大小: {} 字符", totalSize);

        log.info("================================================");
    }

    // 统计方法
    private int countTables(Connection conn, DatabaseScanConfig config) throws SQLException {
        String dbType = config.getDbType().toUpperCase();
        String sql;

        if ("MYSQL".equals(dbType)) {
            sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'";
        } else if ("ORACLE".equals(dbType)) {
            sql = "SELECT COUNT(*) FROM USER_TABLES";
        } else {
            return 0;
        }

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            if ("MYSQL".equals(dbType)) {
                stmt.setString(1, config.getDatabase());
            }
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }

    private int countViews(Connection conn, DatabaseScanConfig config) throws SQLException {
        String dbType = config.getDbType().toUpperCase();
        String sql;

        if ("MYSQL".equals(dbType)) {
            sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = ?";
        } else if ("ORACLE".equals(dbType)) {
            sql = "SELECT COUNT(*) FROM USER_VIEWS";
        } else {
            return 0;
        }

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            if ("MYSQL".equals(dbType)) {
                stmt.setString(1, config.getDatabase());
            }
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }

    private int countIndexes(Connection conn, DatabaseScanConfig config) throws SQLException {
        String dbType = config.getDbType().toUpperCase();
        String sql;

        if ("MYSQL".equals(dbType)) {
            sql = "SELECT COUNT(DISTINCT INDEX_NAME) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = ?";
        } else if ("ORACLE".equals(dbType)) {
            sql = "SELECT COUNT(*) FROM USER_INDEXES";
        } else {
            return 0;
        }

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            if ("MYSQL".equals(dbType)) {
                stmt.setString(1, config.getDatabase());
            }
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }

    private int countProcedures(Connection conn, DatabaseScanConfig config) throws SQLException {
        String sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = ? AND ROUTINE_TYPE = 'PROCEDURE'";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, config.getDatabase());
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }

    private int countFunctions(Connection conn, DatabaseScanConfig config) throws SQLException {
        String sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = ? AND ROUTINE_TYPE = 'FUNCTION'";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, config.getDatabase());
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }

    private int countTriggers(Connection conn, DatabaseScanConfig config) throws SQLException {
        String sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, config.getDatabase());
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }

    private int countTableColumns(Connection conn, String tableName, DatabaseScanConfig config) throws SQLException {
        String dbType = config.getDbType().toUpperCase();
        String sql;

        if ("MYSQL".equals(dbType)) {
            sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
        } else if ("ORACLE".equals(dbType)) {
            sql = "SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ?";
        } else {
            return 0;
        }

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            if ("MYSQL".equals(dbType)) {
                stmt.setString(1, config.getDatabase());
                stmt.setString(2, tableName);
            } else {
                stmt.setString(1, tableName);
            }
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next() ? rs.getInt(1) : 0;
            }
        }
    }

    /**
     * 数据库扫描配置
     */
    private static class DatabaseScanConfig {
        private String host;
        private int port;
        private String database;
        private String schema;
        private String username;
        private String password;
        private String dbType;
        private String dbVersion;
        private String oracleConnectionType = "SERVICE_NAME";

        // DDL提取选项
        private boolean includeTables = true;
        private boolean includeViews = true;
        private boolean includeIndexes = true;
        private boolean includeConstraints = true;
        private boolean includeProcedures = false;
        private boolean includeFunctions = false;
        private boolean includeTriggers = false;

        // 目标表列表
        private List<String> targetTables;

        // Getters and Setters
        public String getHost() {
            return host;
        }

        public void setHost(String host) {
            this.host = host;
        }

        public int getPort() {
            return port;
        }

        public void setPort(int port) {
            this.port = port;
        }

        public String getDatabase() {
            return database;
        }

        public void setDatabase(String database) {
            this.database = database;
        }

        public String getSchema() {
            return schema;
        }

        public void setSchema(String schema) {
            this.schema = schema;
        }

        public String getUsername() {
            return username;
        }

        public void setUsername(String username) {
            this.username = username;
        }

        public String getPassword() {
            return password;
        }

        public void setPassword(String password) {
            this.password = password;
        }

        public String getDbType() {
            return dbType;
        }

        public void setDbType(String dbType) {
            this.dbType = dbType;
        }

        public String getDbVersion() {
            return dbVersion;
        }

        public void setDbVersion(String dbVersion) {
            this.dbVersion = dbVersion;
        }

        public String getOracleConnectionType() {
            return oracleConnectionType;
        }

        public void setOracleConnectionType(String oracleConnectionType) {
            this.oracleConnectionType = oracleConnectionType;
        }

        public boolean isIncludeTables() {
            return includeTables;
        }

        public void setIncludeTables(boolean includeTables) {
            this.includeTables = includeTables;
        }

        public boolean isIncludeViews() {
            return includeViews;
        }

        public void setIncludeViews(boolean includeViews) {
            this.includeViews = includeViews;
        }

        public boolean isIncludeIndexes() {
            return includeIndexes;
        }

        public void setIncludeIndexes(boolean includeIndexes) {
            this.includeIndexes = includeIndexes;
        }

        public boolean isIncludeConstraints() {
            return includeConstraints;
        }

        public void setIncludeConstraints(boolean includeConstraints) {
            this.includeConstraints = includeConstraints;
        }

        public boolean isIncludeProcedures() {
            return includeProcedures;
        }

        public void setIncludeProcedures(boolean includeProcedures) {
            this.includeProcedures = includeProcedures;
        }

        public boolean isIncludeFunctions() {
            return includeFunctions;
        }

        public void setIncludeFunctions(boolean includeFunctions) {
            this.includeFunctions = includeFunctions;
        }

        public boolean isIncludeTriggers() {
            return includeTriggers;
        }

        public void setIncludeTriggers(boolean includeTriggers) {
            this.includeTriggers = includeTriggers;
        }

        public List<String> getTargetTables() {
            return targetTables;
        }

        public void setTargetTables(List<String> targetTables) {
            this.targetTables = targetTables;
        }
    }

    /**
     * 保存DDL提取统计信息到任务中
     */
    private void saveExtractionStatsToTask(ScanTask task, List<SqlStatement> statements,
            DatabaseScanConfig config, long extractionTime) {
        try {
            Map<String, Object> stats = new HashMap<>();

            // 基本统计信息
            stats.put("totalStatements", statements.size());
            stats.put("extractionTimeMs", extractionTime);
            stats.put("extractionTimestamp", System.currentTimeMillis());

            // 数据库信息
            Map<String, Object> dbInfo = new HashMap<>();
            dbInfo.put("host", config.getHost());
            dbInfo.put("port", config.getPort());
            dbInfo.put("database", config.getDatabase());
            dbInfo.put("dbType", config.getDbType());
            dbInfo.put("dbVersion", config.getDbVersion());
            if (config.getSchema() != null) {
                dbInfo.put("schema", config.getSchema());
            }
            stats.put("databaseInfo", dbInfo);

            // 按类型统计DDL对象数量
            Map<String, Long> typeCounts = statements.stream()
                    .collect(Collectors.groupingBy(
                            stmt -> getDdlObjectType(stmt.getSourceFile()),
                            Collectors.counting()));
            stats.put("objectCounts", typeCounts);

            // DDL提取选项
            Map<String, Object> extractionOptions = new HashMap<>();
            extractionOptions.put("includeTables", config.isIncludeTables());
            extractionOptions.put("includeViews", config.isIncludeViews());
            extractionOptions.put("includeIndexes", config.isIncludeIndexes());
            extractionOptions.put("includeConstraints", config.isIncludeConstraints());
            extractionOptions.put("includeProcedures", config.isIncludeProcedures());
            extractionOptions.put("includeFunctions", config.isIncludeFunctions());
            extractionOptions.put("includeTriggers", config.isIncludeTriggers());
            stats.put("extractionOptions", extractionOptions);

            // DDL内容统计
            long totalContentSize = statements.stream()
                    .mapToLong(stmt -> stmt.getContent() != null ? stmt.getContent().length() : 0)
                    .sum();
            stats.put("totalContentSize", totalContentSize);

            // 目标表信息（如果有指定）
            if (config.getTargetTables() != null && !config.getTargetTables().isEmpty()) {
                stats.put("targetTables", config.getTargetTables());
                stats.put("targetTableCount", config.getTargetTables().size());
            }

            // 保存统计信息到任务
            task.setExtractionStats(stats);
            scanTaskRepository.updateById(task);

            log.info("DDL提取统计信息已保存: taskId={}, stats={}", task.getId(), stats);

        } catch (Exception e) {
            log.error("保存DDL提取统计信息失败: taskId={}", task.getId(), e);
        }
    }
}